package cetb.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import cetb.domain.Zone;

public class JDBCZoneDAO implements IZoneDAO {

	public void insert(Connection conn, String zoid, Zone zone) {
		
		String sql = "INSERT INTO zone ( OID, name, city, country) VALUES (?, ?, ?, ?) ";
	    PreparedStatement stmt = null;

	    try {
	    	
	        stmt = conn.prepareStatement(sql);    
	        stmt.clearParameters();
	        stmt.setString(1, zoid);
	        stmt.setString(2, zone.getName());
	        stmt.setString(3, zone.getCity());
	        stmt.setString(4, zone.getCountry());
	                
	        stmt.executeUpdate(); 
	
	    } catch (SQLException e) {
	    	System.out.println("Message: " + e.getMessage());
	        System.out.println("SQLState: " + e.getSQLState());
	        System.out.println("ErrorCode: " + e.getErrorCode());
	    } finally {
	    	try {
	    		if (stmt != null) {
	    			stmt.close();	                
	    		}
	    	} catch (SQLException e) {
	        
	    	}
	    }
		
	}

	public Zone select(Connection conn, String zoneOID) {
		Zone z=new Zone();
        String sql = "SELECT * FROM zone WHERE (OID = ? ) ";
        PreparedStatement stmt = null;
        ResultSet result = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, zoneOID);

            result = stmt.executeQuery();
            
            if(result.next()){
            	z.setName(result.getString("name"));
            	z.setCity(result.getString("city"));
            	z.setCountry(result.getString("country"));
            	
            }
        
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

        return z;
	}

	public String selectZoneOID(Connection conn, Zone zone) {
		String oid="";
        String sql = "SELECT * FROM zone WHERE (name = ? ) AND (city = ?) AND (country = ?) ";
        PreparedStatement stmt = null;
        ResultSet result = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, zone.getName());
            stmt.setString(2, zone.getCity());
            stmt.setString(3, zone.getCountry());

            result = stmt.executeQuery();
            
            if(result.next()){
            	oid=result.getString("OID");
            }
        
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

        return oid;
	}
	
}
